﻿Public Class clsGtNgReasonDetails

    Public Function getGtNgDetailsList(b_gt_sys_id As String, procedure_no As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_ng_reason_details "
        szSql &= "where b_gt_sys_id = ? and validity = ? and procedure_no = ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_sys_id, "1", procedure_no)
        If IsNothing(dt) Then
            getGtNgDetailsList = New DataTable
        Else
            getGtNgDetailsList = dt
        End If
    End Function

    Public Function getGtOperNgDetailsList(b_gt_sys_id As String, procedure_no As String) As DataTable
        Dim szSql As String = ""
        szSql &= " select * from sf.b_gt_ng_reason_details "
        szSql &= "where b_gt_sys_id = ? and validity = ? and procedure_no <> ?"
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, b_gt_sys_id, "1", procedure_no)
        If IsNothing(dt) Then
            getGtOperNgDetailsList = New DataTable
        Else
            getGtOperNgDetailsList = dt
        End If
    End Function

    Public Function getGtNgDetailsLists(idList As String) As DataTable
        Dim szSql As String = "select a.*,b.oper_type,b.type_value,b.assist_marker,concat_ws('',c.size,d.size) as size,f.into_date from sf.b_gt_ng_reason_details a "
        szSql &= "left join sf.b_gt b on a.b_gt_sys_id = b.sys_id "
        szSql &= "left join sf.t_mst_gjb c on b.type_value = c.mst_value and b.oper_type = '0' "
        szSql &= "left join sf.t_mst_flb d on b.type_value = d.mst_value and b.oper_type = '1' "
        szSql &= "left join sf.b_pw_ck_out_details e on b.b_pw_ck_out_details_sys_id = e.sys_id "
        szSql &= "left join sf.b_pw_ck_in_details f on e.batch_no = f.batch_no "
        szSql &= " where a.validity = '1' and a.b_gt_sys_id in (" & idList & ")"
        Return DBacs.ExecuteSql(szSql, -1)
    End Function

    Public Function getGtNgDetailsList(b_gt_sys_id As String) As DataTable
        Dim szSql As String = "select * from sf.b_gt_ng_reason_details where b_gt_sys_id = ? and validity = ?"
        Return DBacs.ExecuteSql(szSql, -1, b_gt_sys_id, "1")
    End Function

    Public Function getGtNgDetailsList(validity As String, dt_start As String, dt_end As String) As DataTable
        Dim szSql As String = "select * from sf.b_gt_ng_reason_details where 1 = 1 and validity = ? and update_time >= ? and update_time <= ?"
        Return DBacs.ExecuteSql(szSql, -1, validity, dt_start, dt_end)
    End Function

    Public Function save(en As entity_gt_ng_reason_details) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            sql &= "INSERT INTO sf.b_gt_ng_reason_details(procedure_No, ng_num, ng_reason, operator, validity, b_gt_sys_id, update_time) "
            sql &= "VALUES ( ?, ?, ?, ?, ?, ?, (SELECT CURRENT_TIMESTAMP(6)))"
            With en
                rtn = DBacs.ExecuteUpdateSql(sql, -1, .procedure_No, .ng_num, .ng_reason, .oper, .validity, .b_gt_sys_id)
            End With
            sql = "UPDATE sf.b_gt_result_details "
            sql &= " SET ok_num = ok_num - ?,ng_num = ng_num + ?, update_time=(SELECT CURRENT_TIMESTAMP(6))"
            sql &= " WHERE b_gt_sys_id = ? and procedure_no = ? "
            DBacs.ExecuteUpdateSql(sql, -1, en.ng_num, en.ng_num, en.b_gt_sys_id, en.procedure_No)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try

        Return rtn
    End Function


    Public Function updateValidity(sys_id As String, ng_num As String, b_gt_sys_id As String, procedure_no As String, oper As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            Dim integer_procedure_No As Integer = Integer.Parse(procedure_no)

            sql &= "UPDATE sf.b_gt_ng_reason_details"
            sql &= " SET validity = ?, operator_validity = ?, update_time_validity = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, "0", oper, sys_id)

            sql = ""
            sql &= "UPDATE sf.b_gt_result_details"
            sql &= " SET ok_num = ok_num + ?, ng_num = ng_num - ?, update_time = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " WHERE procedure_no = ? and b_gt_sys_id = ? "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, ng_num, ng_num, procedure_no, b_gt_sys_id)

            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function
End Class
